package com.face.mapper;

import com.face.entity.Remark;
import com.face.wrapper.RemarkWrapper;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface RemarkMapper {
    // desc是sql关键词，需要转义
    @Insert("insert into tb_remark(remarkId,userId, detail, creator,createTime,updateTime,`desc`) values(#{remarkId},#{userId}, #{detail},#{creator},#{createTime},#{updateTime},#{desc})")
    @SelectKey(statement = "SELECT LAST_INSERT_ID()", keyProperty = "remarkId", before = false, resultType = int.class)
    int saveRemark(Remark remark);

    @Select("select * from tb_remark where userId = #{userId} order by updateTime desc")
    List<Remark> getRemarksByUserId(@Param("userId") String userId);

    //按时间最近的3条记录
    @Select("select * from tb_remark where userId = #{userId} order by updateTime desc limit #{limit}")
    List<Remark> getRecentRemarks(@Param("userId") String userId, @Param("limit") Integer limit);

    @Select("select * from tb_remark a left join tb_user b on a.userId=b.userId where b.shopId = #{shopId} order by a.updateTime desc limit #{limit}")
    List<RemarkWrapper> getRecentRemarksByShopId(@Param("shopId") String shopId, @Param("limit") Integer limit);

    @Select("select * from tb_remark where userId = #{userId} and updateTime between #{start} and #{end}  order by updateTime desc")
    List<Remark> getRemarks(@Param("userId") String userId, @Param("start") String start, @Param("end") String end);

    @Select("select * from tb_remark a left join tb_user b on a.userId=b.userId where b.shopId = #{shopId} order by ${order}")
    List<RemarkWrapper> listAll(@Param("shopId") String shopId, @Param("order") String order);

    @Select("select * from tb_remark a left join tb_user b on a.userId=b.userId where b.shopId = #{shopId} and a.updateTime between #{start} and #{end}  order by ${order}")
    List<RemarkWrapper> list(@Param("shopId") String shopId, @Param("start") String start, @Param("end") String end, @Param("order") String order);

    @Select("select count(*) from tb_remark a left join tb_user b on a.userId=b.userId where b.shopId = #{shopId} and DATE_FORMAT(a.updateTime,'%Y-%m-%d') between DATE_FORMAT(#{start},'%Y-%m-%d') and DATE_FORMAT(#{end},'%Y-%m-%d')")
    Integer count(@Param("shopId") String shopId, @Param("start") String start, @Param("end") String end);

    @Select("select count(*) from tb_remark a left join tb_user b on a.userId=b.userId where b.shopId = #{shopId}")
    Integer countAll(@Param("shopId") String shopId);

    @Select("select * from tb_remark a left join tb_user b on a.userId=b.userId where not exists(select 1 from tb_remark where userId = a.userId and updateTime > a.updateTime) and b.shopId = #{shopId}")
    List<RemarkWrapper> getNewestRemarkList(@Param("shopId") String shopId);

    @Select("select * from tb_remark a left join tb_user b on a.userId=b.userId where not exists(select 1 from tb_remark where userId = a.userId " +
            "and updateTime > a.updateTime) and b.shopId = #{shopId} and a.updateTime>=#{start} and a.updateTime <=#{end}")
    List<RemarkWrapper> getNewestRemarks(@Param("shopId") String shopId, @Param("start") String start, @Param("end") String end);

    @Select("select a.userId, max(a.updateTime) updateTime, count(*) count, b.userName from tb_remark a left join tb_user b on a.userId=b.userId where b.shopId = #{shopId} group by userId")
    List<Map<String, Object>> getRemarkCountList(@Param("shopId") String shopId);

    @Select("select a.userId, max(a.updateTime) updateTime, count(*) count, b.userName from tb_remark a left join tb_user b on a.userId=b.userId " +
            "where b.shopId = #{shopId} and a.updateTime >= #{start} and a.updateTime <= #{end} group by userId")
    List<Map<String, Object>> getRemarkCounts(@Param("shopId") String shopId, @Param("start") String start, @Param("end") String end);

    @Select("select * from tb_remark a left join tb_user b on a.userId=b.userId where b.shopId = #{shopId} and b.userId = #{userId} order by a.updateTime desc")
    List<RemarkWrapper> listAllByUserId(String shopId, String userId);

    @Select("select * from tb_remark a left join tb_user b on a.userId=b.userId where b.shopId = #{shopId} and b.userId = #{userId} and a.updateTime between #{start} and #{end}" +
            " order by a.updateTime desc")
    List<RemarkWrapper> listAllByUserIdAndTime(String shopId, String userId, String start, String end);

    @Select("select DATE_FORMAT(a.createTime,'%Y-%m-%d') date, count(distinct a.userId) count from tb_remark a left join tb_user b on a.userId=b.userId where shopId = #{shopId} and DATE_FORMAT(a.createTime,'%Y-%m-%d') between DATE_FORMAT(#{start},'%Y-%m-%d') and DATE_FORMAT(#{end},'%Y-%m-%d') group by date")
    List<Map<String, Object>> dailyCount(@Param("shopId") String shopId, @Param("start") String start, @Param("end") String end);

    @Select("select DATE_FORMAT(a.createTime,'%Y-%m') date, count(distinct a.userId) count from tb_remark a left join tb_user b on a.userId=b.userId where shopId = #{shopId} and DATE_FORMAT(a.createTime,'%Y-%m') between DATE_FORMAT(#{start},'%Y-%m') and DATE_FORMAT(#{end},'%Y-%m') group by date")
    List<Map<String, Object>> monthlyCount(@Param("shopId") String shopId, @Param("start") String start, @Param("end") String end);

    @Select("select DATE_FORMAT(a.createTime,'%Y-%m-%d') date, count(*) count from tb_remark a left join tb_user b on a.userId=b.userId where shopId = #{shopId} and DATE_FORMAT(a.createTime,'%Y-%m-%d') between DATE_FORMAT(#{start},'%Y-%m-%d') and DATE_FORMAT(#{end},'%Y-%m-%d') group by date")
    List<Map<String, Object>> dailyRemarkCount(@Param("shopId") String shopId, @Param("start") String start, @Param("end") String end);

    @Select("select DATE_FORMAT(a.createTime,'%Y-%m') date, count(*) count from tb_remark a left join tb_user b on a.userId=b.userId where shopId = #{shopId} and DATE_FORMAT(a.createTime,'%Y-%m') between DATE_FORMAT(#{start},'%Y-%m') and DATE_FORMAT(#{end},'%Y-%m') group by date")
    List<Map<String, Object>> monthlyRemarkCount(@Param("shopId") String shopId, @Param("start") String start, @Param("end") String end);

    @Select("select DATE_FORMAT(updateTime,'%Y-%m-%d') date, detail from tb_remark where userId = #{userId} and detail like concat('%',#{keyword},'%') and updateTime between #{start} and date_add(#{end},interval 1 day)  order by updateTime desc")
    List<Map<String, String>> getRemarksForWx(@Param("userId") String userId, @Param("start") String start, @Param("end") String end, @Param("keyword") String keyword);

}
